﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Collections.Generic;
using System.Web.UI.WebControls;
using System.Text.RegularExpressions;

public partial class FormulaPage : System.Web.UI.Page
{
    //(1) when - then validation (2) query generation

    #region init
    private String pageName = String.Empty;

    protected void Page_Load(object sender, EventArgs e)
    {
        //set page name
        pageName = Request.QueryString["PageName"];

        if (!IsPostBack)
        {
            ManageEditable(false);
            ManageVisibles();

            if (LBLHSFormula.SelectedIndex < 0) BEdit.Visible = false;

            if (pageName != FormulaGenerics.FORMULA)
                populateFormulaList();
        }
    }

    private void ManageEditable(Boolean editable)
    {
        //buttons
        BApply.Visible = editable;
        BCancel.Visible = editable;
        BEdit.Visible = !editable;
        BCreate.Visible = !editable;

        //list boxes
        LBListColumn.Enabled = editable;
        LBListFormula.Enabled = editable;
        LBListFunction.Enabled = editable;
        LBListSlabs.Enabled = editable;

        //other controls
        TBFormulaName.Enabled = editable;
        DDLSubType.Enabled = editable;
        DDLReturnType.Enabled = editable;
        TBFromDate.Enabled = editable;
        TBToDate.Enabled = editable;

        //query parts
        TBWhenPart.Enabled = editable;
        TBThenPart.Enabled = editable;
        TBGroupBy.Enabled = editable;
        if (!editable) currentControl.Value = String.Empty;
    }

    private void ManageVisibles()
    {
        //list boxes
        LabelFieldList.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "FieldList");
        LBListColumn.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "FieldList");
        LabelFormulaList.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "FormulaList");
        LBListFormula.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "FormulaList");
        LabelFunctionList.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "FunctionList");
        LBListFunction.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "FunctionList");
        LabelSlabsList.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "SlabsList");
        LBListSlabs.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "SlabsList");

        //other controls
        DDLSubType.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "SubType");
        LabelSubType.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "SubType");
        DDLReturnType.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "ReturnType");
        LabelReturnType.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "ReturnType");
        LabelFromDate.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "FromDate");
        TBFromDate.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "FromDate");
        LabelToDate.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "ToDate");
        TBToDate.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "ToDate");

        //query parts
        LabelWhen.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "WhenPart");
        TBWhenPart.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "WhenPart");
        LabelThen.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "ThenPart");
        TBThenPart.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "ThenPart");
        LabelGroupBy.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "GroupByPart");
        TBGroupBy.Visible = FormulaGenerics.checkIfRequiredOnPage(pageName, "GroupByPart");

        //set page header
        LableTitle.Text = FormulaGenerics.getTitle(pageName);
        return;
    }
    #endregion

    #region display details
    //populate formula details
    protected void LBLHSFormula_DataBinding(object sender, EventArgs e)
    {
        SDSFormula.SelectCommand = FormulaGenerics.getLHSListByType(pageName);
    }

    protected void LBLHSFormula_SelectedIndexChanged(object sender, EventArgs e)
    {
        displayFormula();
        ManageEditable(false);
    }

    private void displayFormula()
    {
        DataRow dr = retrieveDetails();

        if (dr == null) return;
        else displayDetails(dr);

        //formula list is based on SubType, so the formula list box 
        //should be re-retrieved after formula details are updated
        populateFormulaList();
    }

    private DataRow retrieveDetails()
    {
        //if nothing is selected return - do nothing
        if (LBLHSFormula.SelectedIndex < 0) return null;

        //retrieve details in a row
        String query = FormulaGenerics.getDetailsByType(Convert.ToInt32(LBLHSFormula.SelectedValue));
        DataTable dt = DataBaseMethods.ExecuteSelectQuery(query);

        //if there are no rows or there are more than one rows throw error, their must be only one row
        if (dt == null || dt.Rows.Count != 1)
        {
            Logger.Show("There are no entries for selected Formula/Rule.");
            return null;
        }

        return dt.Rows[0];
    }

    private void displayDetails(DataRow entry)
    {
        TBFormulaName.Text = entry["Name"].ToString();
        DDLReturnType.SelectedValue = entry["ReturnType"].ToString();
        DDLSubType.SelectedValue = entry["SubType"].ToString();
        TBFromDate.Text = Convert.ToDateTime(entry["FromDate"].ToString()).ToString(GlobalVariables.DateFormat);
        TBToDate.Text = Convert.ToDateTime(entry["ToDate"].ToString()).ToString(GlobalVariables.DateFormat);
        TBWhenPart.Text = entry["WhenPart"].ToString();
        TBThenPart.Text = entry["ThenPart"].ToString();
        TBGroupBy.Text = entry["GroupByPart"].ToString();
    }
    #endregion

    #region load boxes data
    //populate dropdowns
    protected void DDLSubType_DataBinding(object sender, EventArgs e)
    {
        SDSSubType.SelectCommand = FormulaGenerics.getSubType(pageName);
    }
    protected void DDLReturnType_DataBinding(object sender, EventArgs e)
    {
        SDSReturnType.SelectCommand = FormulaGenerics.getReturnTypes();
    }

    //populate formula list
    protected void DDLSubType_SelectedIndexChanged(object sender, EventArgs e)
    {
        populateFormulaList();
    }
    private void populateFormulaList()
    {
        //get the type
        string SubType = String.Empty;
        if (pageName.Equals(FormulaGenerics.FORMULA)) SubType = DDLSubType.SelectedValue;
        else SubType = pageName;

        String query = FormulaGenerics.getListBySubType(SubType, LBLHSFormula.SelectedValue);
        LBListFormula.DataSource = DataBaseMethods.ExecuteSelectQuery(query);
        LBListFormula.DataBind();
    }

    //populate function list
    protected void LBListFunction_Load(object sender, EventArgs e)
    {
        string query = FormulaGenerics.getFunctionList();
        LBListFunction.DataSource = DataBaseMethods.ExecuteSelectQuery(query);
        LBListFunction.DataBind();
    }

    //populate column list
    protected void LBListField_Load(object sender, EventArgs e)
    {
        string query = FormulaGenerics.getQueriableColumnList(pageName);
        LBListColumn.DataSource = DataBaseMethods.ExecuteSelectQuery(query);
        LBListColumn.DataBind();
    }

    //populate slabs list
    protected void LBListSlabs_Load(object sender, EventArgs e)
    {
        populateSlabsList();
    }
    private void populateSlabsList()
    {
        string query = FormulaGenerics.getSlabsList();
        LBListSlabs.DataSource = DataBaseMethods.ExecuteSelectQuery(query);
        LBListSlabs.DataBind();
    }
    #endregion

    #region buttons at work
    protected void BCancel_Click(object sender, EventArgs e)
    {
        ManageEditable(false);
        displayFormula();
        ViewState["EditMode"] = "None";
    }

    protected void BEdit_Click(object sender, EventArgs e)
    {
        ManageEditable(true);
        ViewState["EditMode"] = "Edit";
    }

    protected void BCreate_Click(object sender, EventArgs e)
    {
        ManageEditable(true);
        resetPage();
        ViewState["EditMode"] = "Create";
    }

    protected void BApply_Click(object sender, EventArgs e)
    {
        //check values entered on page before saving
        if (!arePageElementsValid()) return;
        //save to database
        if (!saveFormulaDetails()) return;
        //set editables false
        ManageEditable(false);
    }

    private void resetPage()
    {
        TBFormulaName.Text = String.Empty;
        populateFormulaList();

        //from to dates
        if (FormulaGenerics.checkIfRequiredOnPage(pageName, "FromDate"))
            TBFromDate.Text = GlobalVariables.Today;
        else
            TBFromDate.Text = GlobalVariables.DefaultFromDate;
        if (FormulaGenerics.checkIfRequiredOnPage(pageName, "ToDate"))
            TBToDate.Text = GlobalVariables.Today;
        else
            TBToDate.Text = GlobalVariables.DefaultToDate;

        //when-then-groupby parts
        TBWhenPart.Text = String.Empty;
        TBThenPart.Text = String.Empty;
        TBGroupBy.Text = String.Empty;

        return;
    }
    #endregion

    #region Validation Section
    private bool arePageElementsValid()
    {
        //name is a must
        if (String.IsNullOrWhiteSpace(TBFormulaName.Text))
        {
            Logger.Show(Logger.MANDATORY_FIELD + "Field: Name.");
            return false;
        }

        //check if dates are mandatory and set
        if (FormulaGenerics.checkIfRequiredOnPage(pageName, "FromDate") || FormulaGenerics.checkIfRequiredOnPage(pageName, "ToDate"))
        {
            if (String.IsNullOrWhiteSpace(TBFromDate.Text) || String.IsNullOrWhiteSpace(TBToDate.Text))
            {
                Logger.Show(Logger.MANDATORY_FIELD + "Field: From/To Date");
                return false;
            }
        }


        //check When/then
        if (FormulaGenerics.checkIfRequiredOnPage(pageName, "WhenPart"))
        {
            if (!validateWhenPart()) return false;
        }
        else
            TBWhenPart.Text = String.Empty;
        if (FormulaGenerics.checkIfRequiredOnPage(pageName, "ThenPart"))
        {
            if (!validateThenPart()) return false;
        }
        else
            TBThenPart.Text = String.Empty;

        //group by is never mandatory, check only if its set
        if (!String.IsNullOrWhiteSpace(TBGroupBy.Text))
        {
            if (!validateGroupBy()) return false;
        }
        else
            TBGroupBy.Text = String.Empty;

        return true;
    }

    protected Boolean validateGroupBy()
    {
        Boolean isValid = true;
        
        //only field names are allowed in group by
        if (!Regex.IsMatch(TBGroupBy.Text, "^((\\w+.\\w+[ ]*)(,[ ]*\\w+.\\w+)?)?$"))
        {
            Logger.Show("Please enter a valid GroupBy Clause. E.g. fieldx, fieldy, ...");
            return false;
        }

        return isValid;
    }

    protected Boolean validateWhenPart()
    {
        Boolean isValid = true;

        //required and empty, return is not valid
        if (String.IsNullOrWhiteSpace(TBWhenPart.Text))
        {
            Logger.Show(Logger.MANDATORY_FIELD + "Field : Condition.");
            isValid = false;
        }

        //perform string replacesment, replace " with ' and ''' with single ' and finally ' with ''
        TBWhenPart.Text = GenericValidations.convertStringQuotes2SQL(TBWhenPart.Text);

        return isValid;
    }

    protected Boolean validateThenPart()
    {
        Boolean isValid = true;

        //required and empty, return is not valid
        if (String.IsNullOrWhiteSpace(TBThenPart.Text))
        {
            Logger.Show(Logger.MANDATORY_FIELD + "Field : Payout.");
            isValid = false;
        }

        //perform string replacesment, replace " with ' and ''' with single ' and finally ' with ''
        TBThenPart.Text = GenericValidations.convertStringQuotes2SQL(TBThenPart.Text);

        return isValid;
    }

    protected void TBFromDate_TextChanged(object sender, EventArgs e)
    {
        //check date & also range
        if (!GenericValidations.validateFromToDates(TBFromDate.Text, TBToDate.Text))
            TBFromDate.Text = String.Empty;
        return;
    }

    protected void TBToDate_TextChanged(object sender, EventArgs e)
    {
        //check date & also range
        if (!GenericValidations.validateFromToDates(TBFromDate.Text, TBToDate.Text))
            TBFromDate.Text = String.Empty;
        return;
    }

    protected void TBFormulaName_TextChanged(object sender, EventArgs e)
    {
        if (!GenericValidations.isValidName(TBFormulaName.Text, LBLHSFormula.Items, ViewState["EditMode"].ToString()))
            TBFormulaName.Text = String.Empty;
        return;
    }
    #endregion

    #region db section
    private Boolean saveFormulaDetails()
    {
        NameValueCollection formulaDetails = new NameValueCollection();
        formulaDetails.Add("Name", TBFormulaName.Text);
        formulaDetails.Add("Type", pageName);
        formulaDetails.Add("SubType", DDLSubType.SelectedValue);
        formulaDetails.Add("FromDate", TBFromDate.Text);
        formulaDetails.Add("ToDate", TBToDate.Text);
        formulaDetails.Add("ReturnType", DDLReturnType.SelectedValue);
        formulaDetails.Add("WhenPart", TBWhenPart.Text);
        formulaDetails.Add("ThenPart", TBThenPart.Text);
        formulaDetails.Add("GroupByPart", TBGroupBy.Text);
        formulaDetails.Add("FinalQuery", "IMPLEMENT");
        formulaDetails.Add("CreatedBy", GlobalVariables.loginId);
        formulaDetails.Add("CreatedOn", DateTime.Now.ToString());

        return performDBOperation(formulaDetails);
    }

    private Boolean performDBOperation(NameValueCollection inputNMV)
    {
        if (ViewState["EditMode"] == null)
        {
            Logger.Show("EditMode not specified. ");
            return false;
        }

        long rowNo = 0;
        try
        {
            if (ViewState["EditMode"].ToString() == "Create")
            {
                String insertCommand = DataBaseMethods.generateInsertQuery("Formula", inputNMV);
                rowNo = DataBaseMethods.ExecuteIdentityInsert(insertCommand, "RowNo");
            }
            else if (ViewState["EditMode"].ToString() == "Edit")
            {
                rowNo = Convert.ToInt64(LBLHSFormula.SelectedValue);
                inputNMV.Add("RowNo", LBLHSFormula.SelectedValue);
                List<String> primary = new List<String> { "RowNo" };
                String updateCommand = DataBaseMethods.generateUpdateQuery("Formula", inputNMV, primary);
                DataBaseMethods.ExecuteScalar(updateCommand);
            }
            else
            {
                Logger.Show("Invalid EditMode - " + ViewState["EditMode"].ToString() + ". Please report to flexipay.bugs@algosis.com.");
            }
        }
        catch (SqlException ex)
        {
            Logger.Show("Error occured while trying to update DB. Error: " + ex.Message);
            return false;
        }

        //reload LHS
        LBLHSFormula.DataBind();
        LBLHSFormula.SelectedValue = rowNo.ToString();
        displayFormula();

        return true;
    }
    #endregion

}